Microsoft Excel 2016 Advanced |Pentagon Training

Call Us

+44 1344 961530

E-Mail Us

info@pentagonit.co.uk

Introduction
Course Details
Course Content
Enquire

A Quick Glance

Learn How to enhanced format your charts, and graphs like professionals work
Find out how to deal with difficult trends in charts and graphs
Find out how to make use of a bar chart and a pie chart to highlight the differences in a data set
Learn the tools and options for exporting your charts and graphs
Learn the limitations of pie charts and the top way to correct them
Study how to use Pivot Tables and Pivot Charts
Study how to put together graphics into your spreadsheets

Microsoft Excel 2016 Advanced training course, follows up from the MS Excel 2016 Intermediate course by delving even deeper into the features and functions of this powerful spreadsheet software. MS Excel 2016 is much more than a rapid way to add up numbers. In this course, Delegates will be trained on advanced features such as nesting IF statements, how to calculate the Mean of a group of numbers using SUMIF, AVERAGEIF, and more.

Delegates will learn about working on lookups with functions such as HLOOKUP and VLOOKUP. This training course will teach delegates about Sparklines, Pivot Tables and Charts. In the end, delegates will learn how to create and record your Macros. At the completion of this Advanced Training for MS Excel 2016, Delegates will have expertise in the advanced features and functions of this software.

Who should take this course

A must for every expert from entry to senior level managers in any business
Engineering & IT graduates
Management graduates
For Delegates looking for entry level jobs in any domain, this is compulsory

Prerequisites

Delegates should be aware of fundamentals of MS Excel.

Master MS Excel Functions and Formulas that saves time and increases productivity
Reduce hours of Hard-work by using Pivot tables while doing analysis data
Find out new tools to share your work
Find new and more efficient ways to analyse data
Add colour and images to improve readability and get messages across to the audience more effectively
Find out the tricks to create professional charts and graphs
Consist of diagrams in marketing reports and sales presentations to boost up sales and readability
Find new ways to visualise data
Trim down monthly workload by automating cyclic tasks by using macros
Importing data with the help of Power Pivot

Course Overview

Microsoft has put add-on very powerful tools to Excel over the previous version of the MS Excel. Know about tools will save your time and energy combing through information. Learn how to create extraordinary charts and graphs (formatting gridlines, presenting basic trends, complex trends, even trends over time). Learn the differences between the variety of types of charts, relationships in data, and ways to demonstrate differences.

Learn different methods to build your exceptions stand out so you can attack the anomalies and make use data effectively to make operations better. MS Excel’s tools are enclosed in depth which includes discussions on setting up live charts, Sparklines, color scales, and icon sets.

features of MS Excel 2016

Course Content

Basic Steps for Charts & Graphs

Overview to Advanced MS Excel 2016
Vital Information for a Successful Training Experience
Steps to Create Graphs and Charts
Additional Features to Help Create Charts and Graphs
MS Excel 2016 keyboard shortcuts

Layout Tab:

Detail Formatting for Charts and Graphs
Formatting Legends within Charts and Graphs
Formatting Axes and Gridlines within Charts and Graphs

Trends

Trends of Charts and Graphs
Complex Trends of Charts and Graphs
Trends over Time of Charts and Graphs

Differences

Show Differences using Bar Charts
Show Differences by using Pie Charts
Restrictions with Pie Charts & How to Correct
Substitute Ways to Show Differences

Relationships:

Using Charts and Graphs to Show Relationships

Stock:

Charting & Graphing Financial Information
Specific Charts for Finance Information
Setting Up Live Charts

The MS Excel IF Function

Syntax of IF
Nesting The IF Statement In Excel
Use AND Operator To Reduce the number Of Nested IF’s
Use OR Operator To Reduce the number Of Nested IF’s
The NOT Operator within AND and OR Statements
SUMIF for Selective Adding Up
COUNTIF for Selective Counting
AVERAGEIF for The Mean Of Cells That Meet Our Criteria
Multiple Criteria of The Same SUM, AVERAGE And COUNT Functions

Performing Lookup in Excel

VLOOKUP
Examples for VLOOKUP
HLOOKUP
HLOOKUP In Action
Look For A Near Match In A Lookup
Check Missing Data In A Lookup
Extend The Size Of A Lookup Table
Nested LOOKUPs In Excel

Excel Data Functions

The MATCH Function
The INDEX Function Syntax
How To Stop Non-existent Row or Column Lookups In INDEX
The CHOOSE Lookup Function

Excel Math Functions

Handling TIME
Rounding To Fractional Values
MOD For Working Out Remainders
Generate A Random Number
Select up List Item at Random
Calculation of a Loan Repayments Using PMT
Excel - Investment Calculations Using PMT
Study Depreciation
Learn how to Different Parts Of A Loan Calculation

Arrays In Excel

Overview of an Array and Array Formula
Create And use an Array Formula
Conditional Evaluation in an Array Formula
TRANSPOSE Array Function

Sparklines

Create A Sparkline In Excel
Change The Design Of Sparklines
Deal with Empty Cells
Compare One Sparkline To Another by Altering Vertical Scale
Remove Sparklines from a Sheet

Worksheet and Workbook Protection

Understanding Protection
Encrypting Files with Passwords
Allowing Specific Worksheet Changes
Adding Protection to Selected Cells
Additional Protection Features

Advanced Formatting

Enhancing Worksheets with the use of Themes
Work with the Comments
Create users AutoFill List
Turn your data into the table for formatting

Automate processes with Macros

Macros
Display the Developer Tab
Create a Basic Formatting Macro
Run a Macro
Assign a Macro to a Button
Create Complex Macros
View and Editing the VBA Code
Add a Macro to the Quick Access Toolbar

Form Controls

What are Form Controls?
Adding Spin Buttons and Check Boxes
Adding a Combo Box

Shortcuts of Excel

Shortcuts for the MS Excel:

To do this	Press
Close a spreadsheet	Ctrl+W
Open a spreadsheet	Ctrl+O
Go to the Home tab	Alt+H
Save a spreadsheet	Ctrl+S
Copy	Ctrl+C
Paste	Ctrl+V
Undo	Ctrl+Z
Remove cell contents	Delete key
Choose a fill color	Alt+H, H
Cut	Ctrl+X
Go to Insert tab	Alt+N
Bold	Ctrl+B
Center align cell contents	Alt+H, A, then C
Go to Page Layout tab	Alt+P
Go to Data tab	Alt+A
Go to View tab	Alt+W
Format a cell from context menu	Shift+F10, or Context key
Add borders	Alt+H, B
Delete column	Alt+H, D, then C
Go to Formula tab	Alt+M

Vlookup(): It helps to search a value in a table and returns a corresponding value.

Syntax: = LOOKUP( value, lookup_range, [result_range] )

HLOOKUP(): The MS Excel HLOOKUP function performs a search for a value in the top row of the table and returns the value in the same column by index_number.

HLOOKUP( value, table, index_number, [approximate_match] )

Microsoft Excel 2016 Advanced Enquiry

Enquire Now

----- OR -------

Reach us at +44 1344 961530 or info@pentagonit.co.uk for more information.

About Kingston upon Hull

Kingston upon Hull also known as Hull is a city and unitary authority located in East Riding of Yorkshire, England. It is situated on River Hull, 40 km inland from the North Sea and has a population of 260,220. In the 12^th century, this town was discovered. Monks of Meaux Abbey required a port from wool from estates could be exported. These monks choose a place at the confluence of River Humber and Hull to construct quay.

Year in which this town was discovered is not yet known, but it was first mentioned in 1193. It was renamed Kings-town-upon Hull by King Edward 1 in 1299. Hull has been a military support port, market town, whaling and fishing centre and industrial metropolis. In English Civil Wars, Hull was an early theatre of battle. William Wilberforce took a significant part in the abolition of slave trade done in Britain.

From 1902, the city is unique in the United Kingdom has had a municipally owned telephone system. After facing massive damage during Second World War. At the beginning of 21^st century, after facing recession in the late 2000s, the city saw a large amount of new commercial, retail, public and housing service construction spending.

Tourist attractions of Kingston Upon Hull include Museum Quarter, Old Town, The Deep, Hull Marina that is a city landmark. Redevelopment of Ferensway, include the opening of St Stephen’ Hull and new Hull Truck Theatre. Sports in Kingston Upon Hull include Football and Rugby. KCOM study houses Hull City football club and Hull FC rugby league club. Hull is home to English Premier Ice Hockey League Hull Pirates.

The University of Hull was discovered in 1927, and now It has more than 16000 students. It is ranked as the best university in the Yorkshire and Humber region, and it is located in Newland suburb that is in the north-west of the city.

In 2013, it was announced that Hull would be 2017 UK City of Culture.

In 2015, it was announced that Ferens Art Gallery would host prestigious annual art prize called the Turner Prize in 2017. After this, the prize was held outside London every year.

History

The population of Hull in 1901 was 239,000, and it continued to increase. City Hall was built in 1909 and Guildhall in 1916. During 20^th century facilities in Hull improved. In the 1920s and 1930s, slum clearance started in the centre of Hull. Many new council houses were built on West, North and East of the city. At the beginning of 20^th-century various houses in Hull didn’t have flushing facility. In 1929, fences of Hull were extended to include part of Anlaby and Sutton. Queens’s gardens were laid out on a site filled in the dock. Hull suffered depression in the 1930s, and many Dockers got unemployed, similarly in the shipbuilding industry. In 1954, Hull University was founded. In 2001 an aquarium called The Deep was opened in Hull. Later at the beginning of 21^st century, various parts of Hull were regenerated. Fish Market of Hull was closed in 2011. In 2017 Hull became UK City of Culture. Now the population of Hull is 258,000.